package com.woniu.atms.mapper;

import com.woniu.atms.entity.G5Route;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;
import org.apache.ibatis.mapping.FetchType;

import java.util.List;

/**
  *@title RouteMapper 
  *@author LHY
  *@createTime 2022/1/18/17:09 
 **/
public interface RouteMapper {

    /**
     * 查询所有路线
     * @return
     */
    @Select("select * from g5_route where is_exit='y' ")
    @Results({
            @Result(id = true,property = "id",column = "id"),
            //查询起始站点信息
            @Result(property = "startSite",column = "start_site_id",
                    one=@One(select = "com.woniu.atms.mapper.SiteMapper.findSiteById",
                            fetchType = FetchType.LAZY)),
            //查询终点站信息
            @Result(property = "endSite",column = "end_site_id",
                    one=@One(select = "com.woniu.atms.mapper.SiteMapper.findSiteById",
                            fetchType = FetchType.LAZY))
    })
    public List<G5Route> findAllRoute();

    /**
     * 根据id查询路线
     * @param id
     * @return
     */
    @Select("select * from g5_route where id=#{id} and is_exit='y'")
    @Results({
            @Result(id = true,property = "id",column = "id"),
            //查询起始站点信息
            @Result(property = "startSite",column = "start_site_id",
            one=@One(select = "com.woniu.atms.mapper.SiteMapper.findSiteById",
                    fetchType = FetchType.LAZY)),
            //查询终点站信息
            @Result(property = "endSite",column = "end_site_id",
            one=@One(select = "com.woniu.atms.mapper.SiteMapper.findSiteById",
                    fetchType = FetchType.LAZY))
    })
    public G5Route findRouteById(int id);

    /**
     * 根据路线状态查询路线
     * @return
     */
    @Select("select * from g5_route where is_exit='y' and status=#{status}")
    @Results({
            @Result(id = true,property = "id",column = "id"),
            //查询起始站点信息
            @Result(property = "startSite",column = "start_site_id",
                    one=@One(select = "com.woniu.atms.mapper.SiteMapper.findSiteById",
                            fetchType = FetchType.LAZY)),
            //查询终点站信息
            @Result(property = "endSite",column = "end_site_id",
                    one=@One(select = "com.woniu.atms.mapper.SiteMapper.findSiteById",
                            fetchType = FetchType.LAZY))
    })
    public List<G5Route> findRouteByStatus(String status);

    /**
     * 改变路线状态和删除路线
     * @param route
     * @return
     */
    @UpdateProvider(value = RouteSql.class,method = "updateRoute")
    public boolean updateRoute(G5Route route);

    /**
     * 根据起点和终点的站点id数组查询路线
     * @param startSitIds
     * @param endSitIds
     * @return
     */
    @Select({
            "<script>",
            "select",
            "*",
            "from g5_route",
            "where start_site_id in",
            "<foreach collection='startSitIds' item='startSitId' open='(' separator=',' close=')'>",
            "#{startSitId}",
            "</foreach>",
            "and end_site_id in",
            "<foreach collection='endSitIds' item='endSitId' open='(' separator=',' close=')'>",
            "#{endSitId}",
            "</foreach>",
            "and is_exit='y' ",
            "</script>"
    })
    @Results({
            @Result(id = true,property = "id",column = "id"),
            //查询起始站点信息
            @Result(property = "startSite",column = "start_site_id",
                    one=@One(select = "com.woniu.atms.mapper.SiteMapper.findSiteById",
                            fetchType = FetchType.LAZY)),
            //查询终点站信息
            @Result(property = "endSite",column = "end_site_id",
                    one=@One(select = "com.woniu.atms.mapper.SiteMapper.findSiteById",
                            fetchType = FetchType.LAZY))
    })
    public List<G5Route> findRoutesByStarAndEndAddress(@Param("startSitIds") List<Integer> startSitIds,@Param("endSitIds") List<Integer> endSitIds);


    /**
     * 添加路线
     * @param route
     * @return
     */
    @Insert("INSERT INTO g5_route(route_name,start_site_id,end_site_id,spend_time,status,is_exit)" +
            "VALUES(#{routeName},#{startSite.id},#{endSite.id},#{spendTime},#{status},#{isExit})")
    public boolean addRoute(G5Route route);

    /**
     * 按路线名查询线路
     * @param name
     * @return
     */
    @Select("SELECT * FROM g5_route WHERE route_name=#{routeName}")
    public G5Route findRouteByName(String name);

    //动态sql
    public class RouteSql{
        public String updateRoute(G5Route route){
            SQL sql=new SQL();
            sql.UPDATE("g5_route");
            if (route.getStatus()!=null && route.getStatus().trim().length() != 0 ) {
                sql.SET("status=#{status}");
            }
            if(route.getIsExit()!=null && route.getIsExit().trim().length() != 0){
                sql.SET("is_exit=#{isExit}");
            }
            sql.WHERE("id=#{id}");
            return sql.toString();
        }
    }

}
